﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tauren.Business.Entity.Enum;
using Tauren.Business.Entity.Model;
using Tauren.Business.Entity.Output;
using Tauren.Business.Infrastructure.Globals;
using emp = Tauren.Business.Entity.Model;
using Dapper.Contrib.Extensions;

namespace Tauren.Business.DLL.Employee
{
    public class EmployeeBusiness : Base.BaseDLL, IEmployeeBusiness
    {
        public EmployeeBusiness(IConnectionDLLBase _dLLBase) : base(_dLLBase)
        {
        }

        /// <summary>
        /// 删除人员基本信息信息
        /// </summary>
        /// </summary>
        /// <param name="model">人员基本信息实体</param>
        /// <returns></returns>
        public bool Delete(object id)
        {
            return dbConnection.Execute(sql: "UPDATE Employee SET Status=@Status,Enable=@Enable WHERE EmpId=@EmpId", param: new emp.Employee() { EmpId = (int)id, Status = (int)Entity.Enum.EmpStauts.Dimission, Enable = false }) >= 0;
        }

        /// <summary>
        /// 获取人员基本信息信息--多个-按工号
        /// </summary>
        /// </summary>
        /// <param name="model">人员基本信息实体</param>
        /// <returns></returns>
        public emp.Employee GetByCode(string code)
        {
            return dbConnection.Query<emp.Employee>(sql: "SELECT *  FROM   Employee WHERE empcode=@EmpCode", param: new emp.Employee() { EmpCode = code }).FirstOrDefault();
        }

        /// <summary>
        /// 获取人员基本信息信息--根据EmpIds集合
        /// </summary>
        /// </summary>
        /// <param name="model">人员基本信息实体</param>
        /// <returns></returns>
        public Dictionary<int, string> GetListByEmpIds(List<string> EmpIds)
        {
            Dictionary<int, string> dics = new Dictionary<int, string>();
            if (EmpIds.Any())
            {
                string EmpId = string.Join(",", EmpIds);
                var List = dbConnection.Query<emp.Employee>(sql: string.Format("SELECT *  FROM   Employee WHERE EmpId in({0})", EmpId)).AsList();
                List.ForEach(o =>
                {
                    if (!dics.Any(p => p.Key == o.EmpId))
                    {
                        dics.Add(o.EmpId, Global.IsChinese ? o.CNName : o.ENName);
                    }
                });
            }
            return dics;
        }
        /// <summary>
        /// 获取人员基本信息信息--获取所有集合
        /// </summary>
        /// </summary>
        /// <param name="model">人员基本信息实体</param>
        /// <returns></returns>
        public Dictionary<int, string> GetAll()
        {
            Dictionary<int, string> dics = new Dictionary<int, string>();
            var List = base.All<emp.Employee>();
            List.ForEach(o =>
            {
                if (!dics.Any(p => p.Key == o.EmpId))
                {
                    dics.Add(o.EmpId, Global.IsChinese ? o.CNName : o.ENName);
                }
            });
            return dics;
        }

        /// <summary>
        /// 物理删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool PhysicalDelete(int id)
        {
            return dbConnection.Execute(sql: "DELETE FROM  Employee WHERE EmpId=@EmpId", param: new emp.Employee() { EmpId = (int)id }) >= 0;
        }

        /// <summary>
        /// 启用/禁用
        /// </summary>
        /// <param name="id"></param>
        /// <param name="enable"></param>
        /// <returns></returns>
        public bool Enable(int id, bool enable)
        {
            return dbConnection.Execute(sql: "UPDATE Employee SET Enable=@Enable WHERE EmpId=@EmpId", param: new emp.Employee() { EmpId = (int)id, Enable = enable }) >= 0;
        }

        /// <summary>
        /// 根据职位获取员工
        /// </summary>
        /// <param name="PostionId">职位id</param>
        /// <returns></returns>
        public List<emp.PageEployeeModel> Pages(Entity.Global.QueryModel queryModel, out long Total)
        {
            Total = 0;
            if (!queryModel.paramters.Any(p => p.Label.ToLower() == OrgationalType.position.ToString())&& !queryModel.KeyWords.Any(p => p.Label.ToLower() == OrgationalType.position.ToString())) return new List<emp.PageEployeeModel>();
            string where = "";
            if (!string.IsNullOrEmpty(queryModel.KeyWord))
            {
                where += $" AND (t.CNNAME LIKE '%{queryModel.KeyWord}%' OR t.EMPCODE LIKE '%{queryModel.KeyWord}%' OR t.ENNAME LIKE '%{queryModel.KeyWord}%' ) ";
            }
            if (string.IsNullOrEmpty(queryModel.SortField))
            {
                queryModel.SortField = "t.EmpId";
                queryModel.IsAsc = false;
            }
            else
            {
                queryModel.SortField = queryModel.SortField.ToLower().Replace("dic_", "").Replace("dic", "").Trim();
            }
            string mainPositionSql = "";
            string parttimePositionsql = "";
            if (queryModel.paramters.Any())
            {
                var pos = queryModel.paramters.FirstOrDefault(p => p.Label.ToLower() == OrgationalType.position.ToString());
                if (pos != null && !string.IsNullOrEmpty(pos.Value + ""))
                {
                    mainPositionSql = $" AND  e.positionId = {pos.Value}";
                    parttimePositionsql = $" AND  p.positionId = {pos.Value}";
                }
                var paramtets = queryModel.paramters.Where(o => o.Label.ToLower() != OrgationalType.position.ToString()).ToList();
                paramtets.ForEach(p =>
                {
                    where += $"  and {p.Label} like '%{p.Value}%'  ";
                });
            }

            if (queryModel.KeyWords.Any())
            {
                var pos = queryModel.KeyWords.FirstOrDefault(p => p.Label.ToLower() == OrgationalType.position.ToString());
                if (pos != null && !string.IsNullOrEmpty(pos.Value + ""))
                {
                    mainPositionSql = $" AND  e.positionId = {pos.Value}";
                    parttimePositionsql = $" AND  p.positionId = {pos.Value}";
                }

            }
            if (queryModel.ExceptDataField.Any())
            {
                queryModel.ExceptDataField.ForEach(o =>
                {
                    mainPositionSql += $" AND e.{o.Label}!='{o.Value}'";
                    parttimePositionsql += $" AND e.{o.Label}!='{o.Value}'";
                });
            }
            if (!queryModel.UseDisabledData)
            {
                where += $" AND t.enable=1  ";
            }
            queryModel.Page = queryModel.Page == 0 ? 1 : queryModel.Page;
            string asc = queryModel.IsAsc ? "ASC" : "DESC";
            StringBuilder sql = new StringBuilder();
            sql.Append(@$"select count(1) from (  select
	                                        *,
	                                        0 as onparttime
                                        from
	                                        employee e
                                        where
	                                       
	                                         (e.havedelete = 0
		                                        or e.havedelete is null )
	                                       {mainPositionSql}
                                        union 
                                        select
	                                        e.*,
	                                        1 as onparttime
                                        from
	                                        parttimeinfo p
                                        left join employee e on
	                                        e.empid = p.empid
                                        where 
	                                         (e.havedelete = 0
		                                        or e.havedelete is null )
	                                         {parttimePositionsql}) t where  t.status <>  {(int)Entity.Enum.EmpStauts.Dimission} {where};");

            sql.Append(@$"select * from (  select
	                                        *,
	                                        0 as onparttime
                                        from
	                                        employee e
                                        where
	                                       
	                                         (e.havedelete = 0
		                                        or e.havedelete is null )
	                                       {mainPositionSql}
                                        union 
                                        select
	                                        e.*,
	                                        1 as onparttime
                                        from
	                                        parttimeinfo p
                                        left join employee e on
	                                        e.empid = p.empid
                                        where 
	                                         (e.havedelete = 0
		                                        or e.havedelete is null )
	                                         {parttimePositionsql}) t where  t.status <> {(int)Entity.Enum.EmpStauts.Dimission} 
                        {where}  ORDER BY {queryModel.SortField} {asc} LIMIT {(queryModel.Page - 1) * queryModel.PageSize},{queryModel.PageSize} ;");
            using (var reader = dbConnection.QueryMultiple(sql.ToString()))
            {
                Total = reader.ReadFirstOrDefault<int>();
                List<emp.PageEployeeModel> eployeeModels = reader.Read<emp.PageEployeeModel>().ToList();
                long RowNo = ((queryModel.Page == 0 ? 1 : queryModel.Page) - 1) * queryModel.PageSize + 1;
                eployeeModels.ForEach(o =>
                {
                    o.RowNo = RowNo;
                    RowNo++;
                });
                return eployeeModels;
            }
        }

        /// <summary>
        /// 根据组织获取员工基本信息
        /// </summary>
        /// <param name="OrgId"></param>
        /// <returns></returns>
        public List<emp.Employee> GetByOrgs(string OrgId)
        {
            StringBuilder stringBuilder = new StringBuilder(@$"SELECT * FROM employee WHERE orgId in({OrgId}) and `enable`=1 AND `status` <>0
                                                            UNION 
                                                            SELECT a.* FROM employee a LEFT JOIN parttimeinfo b on a.empid=b.empid 
                                                            WHERE b.orgid in({OrgId}) and  a.`enable`=1 AND a.`status` <>0");
            return dbConnection.Query<emp.Employee>(stringBuilder.ToString()).ToList();
        }

        public List<Parttimeinfo_OutPut> Parttimes(int EmpId) => dbConnection.Query<Parttimeinfo_OutPut>("SELECT p1.*,p2.cnname,p2.enname FROM parttimeinfo p1 LEFT JOIN position p2 ON p1.positionid=p2.positionId  WHERE p1.empid=@EmpId", new Parttimeinfo() { EmpId = EmpId }).ToList();

        public bool Insert(List<Parttimeinfo> parttimeinfos, int EmpId)
        {
            dbConnection.Execute(sql: "DELETE FROM  parttimeinfo WHERE EmpId=@EmpId", param: new emp.Parttimeinfo() { EmpId = EmpId });
            if (parttimeinfos.Any())
            {
                return dbConnection.Insert(parttimeinfos) >= 0;
            }
            else return true;
            throw new NotImplementedException();
        }

        public double GetSeq()
        {
            Position organization = this.dbConnection.QueryFirstOrDefault<Position>("SELECT * FROM employee WHERE (havedelete = 0 or havedelete is null)  ORDER BY empId DESC LIMIT 0,1");
            if (organization is null) return 1;
            else return (organization.Sequence is null ? 1 : organization.Sequence.Value + 1);
        }
        /// <summary>
        /// 根据职位获取员工
        /// </summary>
        /// <param name="PostionId">职位id</param>
        /// <returns></returns>
        public List<emp.PageEployeeModel> DialogPages(Entity.Global.QueryModel queryModel, out long Total)
        {
            Total = 0;
            if (!queryModel.paramters.Any(p => p.Label.ToLower() == OrgationalType.position.ToString())) return new List<emp.PageEployeeModel>();
            string where = "";
            if (!string.IsNullOrEmpty(queryModel.KeyWord))
            {
                where += $" AND (t.CNNAME LIKE '%{queryModel.KeyWord}%' OR t.EMPCODE LIKE '%{queryModel.KeyWord}%' OR t.ENNAME LIKE '%{queryModel.KeyWord}%' ) ";
            }
            if (string.IsNullOrEmpty(queryModel.SortField))
            {
                queryModel.SortField = "t.EmpId";
                queryModel.IsAsc = false;
            }
            else
            {
                queryModel.SortField = $"t.{queryModel.SortField}";
            }
            string mainPositionSql = "";
            string parttimePositionsql = "";
            if (queryModel.paramters.Any())
            {
                var pos = queryModel.paramters.FirstOrDefault(p => p.Label.ToLower() == OrgationalType.position.ToString());
                if (pos != null && !string.IsNullOrEmpty(pos.Value + ""))
                {
                    mainPositionSql = $" AND  e.positionId = {pos.Value}";
                    parttimePositionsql = $" AND  p.positionId = {pos.Value}";
                }
            }
            if (queryModel.ExceptDataField.Any())
            {
                queryModel.ExceptDataField.ForEach(o =>
                {
                    mainPositionSql += $" AND e.{o.Label}!='{o.Value}'";
                    parttimePositionsql += $" AND e.{o.Label}!='{o.Value}'";
                });
            }
            if (!queryModel.UseDisabledData)
            {
                where += $" AND t.enable=1  ";
            }
            queryModel.Page = queryModel.Page == 0 ? 1 : queryModel.Page;
            string asc = queryModel.IsAsc ? "ASC" : "DESC";
            StringBuilder sql = new StringBuilder();
            sql.Append(@$"select count(1) from (  select
	                                        *,
	                                        0 as onparttime
                                        from
	                                        employee e
                                        where
	                                       
	                                         (e.havedelete = 0
		                                        or e.havedelete is null )
	                                       {mainPositionSql}
                                        union 
                                        select
	                                        e.*,
	                                        1 as onparttime
                                        from
	                                        parttimeinfo p
                                        left join employee e on
	                                        e.empid = p.empid
                                        where 
	                                         (e.havedelete = 0
		                                        or e.havedelete is null )
	                                         {parttimePositionsql}) t where  t.status <>  {(int)Entity.Enum.EmpStauts.Dimission} {where};");

            sql.Append(@$"select * from (  select
	                                        *,
	                                        0 as onparttime
                                        from
	                                        employee e
                                        where
	                                       
	                                         (e.havedelete = 0
		                                        or e.havedelete is null ) and   e.empid not in(SELECT empid from accoutinfo where enable=1)
	                                       {mainPositionSql}
                                        union 
                                        select
	                                        e.*,
	                                        1 as onparttime
                                        from
	                                        parttimeinfo p
                                        left join employee e on
	                                        e.empid = p.empid
                                        where 
	                                         (e.havedelete = 0
		                                        or e.havedelete is null ) and   e.empid not in(SELECT empid from accoutinfo where enable=1)
	                                         {parttimePositionsql}) t where  t.status <> {(int)Entity.Enum.EmpStauts.Dimission} 
                        {where}  ORDER BY {queryModel.SortField} {asc} LIMIT {(queryModel.Page - 1) * queryModel.PageSize},{queryModel.PageSize} ;");
            using (var reader = dbConnection.QueryMultiple(sql.ToString()))
            {
                Total = reader.ReadFirstOrDefault<int>();
                List<emp.PageEployeeModel> eployeeModels = reader.Read<emp.PageEployeeModel>().ToList();
                long RowNo = ((queryModel.Page == 0 ? 1 : queryModel.Page) - 1) * queryModel.PageSize + 1;
                eployeeModels.ForEach(o =>
                {
                    o.RowNo = RowNo;
                    RowNo++;
                });
                return eployeeModels;
            }
        }

        /// <summary>
        /// 流程审批人所在列表
        /// </summary>
        /// <param name="queryModel"></param>
        /// <param name="Total"></param>
        /// <returns></returns>
        public List<emp.PageEployeeModel> FlowApproverPages(Entity.Global.QueryModel queryModel, out long Total)
        {
            Total = 0;

            string where = "";
            if (!string.IsNullOrEmpty(queryModel.KeyWord))
            {
                where += $" AND (e.CNNAME LIKE '%{queryModel.KeyWord}%' OR e.EMPCODE LIKE '%{queryModel.KeyWord}%' OR e.ENNAME LIKE '%{queryModel.KeyWord}%' ) ";
            }
            if (string.IsNullOrEmpty(queryModel.SortField))
            {
                queryModel.SortField = "e.EmpId";
                queryModel.IsAsc = false;
            }
            else
            {
                queryModel.SortField = $"e.{queryModel.SortField}";
            }
            if (queryModel.paramters.Any())
            {
                ///如果是职位
                var pos = queryModel.paramters.FirstOrDefault(p => p.Label.ToLower() == OrgationalType.position.ToString());
                if (pos != null && !string.IsNullOrEmpty(pos.Value + ""))
                {
                    string[] posIds = pos.Value.ToString().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                    if (posIds.Any())
                    {
                        where += $" AND  e.positionId in ('{(string.Join("','", posIds))}')";
                    }
                }

                ///如果是人员
                var emp = queryModel.paramters.FirstOrDefault(p => p.Label.ToLower() == OrgationalType.personal.ToString());
                if (emp != null && !string.IsNullOrEmpty(emp.Value + ""))
                {
                    where += $" AND  e.empid in ({emp.Value})";
                }
            }
            if (!queryModel.UseDisabledData)
            {
                where += $" AND e.enable=1  ";
            }
            queryModel.Page = queryModel.Page == 0 ? 1 : queryModel.Page;
            string asc = queryModel.IsAsc ? "ASC" : "DESC";
            StringBuilder sql = new StringBuilder();
            sql.Append(@$"select count(1) from employee e  where e.status<> {(int)Entity.Enum.EmpStauts.Dimission} and  (e.havedelete = 0  or e.havedelete is null ) {where};");

            sql.Append(@$"select *,  0 as onparttime from  employee e where e.status<> {(int)Entity.Enum.EmpStauts.Dimission} and  (e.havedelete = 0  or e.havedelete is null ) {where}   ORDER BY {queryModel.SortField} {asc} LIMIT {(queryModel.Page - 1) * queryModel.PageSize},{queryModel.PageSize} ;");
            using (var reader = dbConnection.QueryMultiple(sql.ToString()))
            {
                Total = reader.ReadFirstOrDefault<int>();
                List<emp.PageEployeeModel> eployeeModels = reader.Read<emp.PageEployeeModel>().ToList();
                long RowNo = ((queryModel.Page == 0 ? 1 : queryModel.Page) - 1) * queryModel.PageSize + 1;
                eployeeModels.ForEach(o =>
                {
                    o.RowNo = RowNo;
                    RowNo++;
                });
                return eployeeModels;
            }
        }
    }
}
